package utils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import clases.Usuario;

public class ConsultasDB {

	public static List<Usuario> RecuperarUsuarios(Connection connection)
	{
		try {
			
			if(!TablaExiste("USUARIOS",connection))
			{		
				CrearTablaUsuarios(connection);
			}
			
			List<Usuario> listaUsuarios = new ArrayList<Usuario>();
			Statement stmt;
				stmt = connection.createStatement();
			ResultSet rset = 
			          stmt.executeQuery("select * from USUARIOS ORDER BY id");
			while (rset.next())
			{
				Usuario user = new Usuario();
				user.setId(rset.getString("ID"));
				user.setNombre(rset.getString("NOMBRE"));
				user.setPuntuacion(rset.getString("PUNTUACION"));
				listaUsuarios.add(user);
			}	
			stmt.close();
			return listaUsuarios;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	private static void CrearTablaUsuarios(Connection connection) {
		try {		
			Statement stmt;
			stmt = connection.createStatement();
			stmt.executeQuery("CREATE TABLE USUARIOS (id number, nombre varchar2(100), puntuacion number)");	
			stmt.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}		
	}

	public static boolean TablaExiste(String nombreTabla,Connection connection)
	{
		 try {		
			 	boolean resultado = false;
				Statement stmt;
				stmt = connection.createStatement();
				ResultSet rset = stmt.executeQuery("SELECT COUNT(1) FROM ALL_TABLES WHERE TABLE_NAME = '"+ nombreTabla +"'");	
				while (rset.next())
				{					
					if (rset.getInt(1)==0)
					{
						resultado = false;
					}
					else
					{
						resultado = true;
					}
				}
				stmt.close();
				return resultado;
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}
	}
	
	public static void crearUsuario(Connection connection, String nombreUsuario) {
		try {		
			int ultimoID = 0;
			Statement stmt;
			stmt = connection.createStatement();
			ResultSet rset = stmt.executeQuery("SELECT id FROM USUARIOS ORDER BY id");
			while (rset.next())
			{	
				ultimoID = rset.getInt(1);
			}
			ultimoID++;
			stmt.executeQuery("INSERT INTO USUARIOS VALUES("+ ultimoID +", '"+ nombreUsuario +"', 0)");	
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}		
	}
	
	public static void borrarUsuario(Connection connection, String ID) {
		try {		
			Statement stmt;
			stmt = connection.createStatement();
			stmt.executeQuery("DELETE FROM USUARIOS Where id="+ID);
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}		
	}
	
	public static void sumarVictoria(Connection connection, String ID) {
		try {		
			int puntacionAnterior = 0;
			Statement stmt;
			stmt = connection.createStatement();
			ResultSet rset = stmt.executeQuery("SELECT puntuacion FROM USUARIOS Where id="+ID);
			while (rset.next())
			{	
				puntacionAnterior = rset.getInt(1);
			}
			puntacionAnterior++;			
			stmt.executeQuery("UPDATE USUARIOS SET puntuacion="+puntacionAnterior+" where id="+ID);
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}		
	}
}
